﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using OgilvyOne.IDAL;
using OgilvyOne.Model;
using OgilvyOne.Utility;

namespace OgilvyOne.SqlServerDAL
{
    public class MemberInfo : IMemberInfo
    {
        public const string COFCO_MEMBER_TYPE = "1";
        private const string ADD_SQL = "insert into COFCO_MEMBER_INFO(USERNAME,MEMBER_NICKNAME,PASSWORD,MEMBER_NAME,USER_TYPE,TEL,MOBLIE,POSTCODE,ADDRESS,REG_TIME,IP,LAST_LOGIN_TIME,STATE) values(@USERNAME,@MEMBER_NICKNAME,@PASSWORD,@MEMBER_NAME,@USER_TYPE,@TEL,@MOBLIE,@POSTCODE,@ADDRESS,getDate(),@IP,getDate(),0);select @@IDENTITY";
        //添加
        public int Add(MemberInfoModel model)
        {
            
            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter("@USERNAME", SqlDbType.VarChar,200),
                new SqlParameter("@MEMBER_NICKNAME",SqlDbType.VarChar,100),
                new SqlParameter("@PASSWORD",SqlDbType.VarChar,50), 
                new SqlParameter("@MEMBER_NAME",SqlDbType.VarChar,50),
                new SqlParameter("@USER_TYPE",SqlDbType.VarChar,20),
                new SqlParameter("@TEL", SqlDbType.VarChar, 20),
                new SqlParameter("@MOBLIE",SqlDbType.VarChar,20),
                new SqlParameter("@POSTCODE",SqlDbType.VarChar,10),
                new SqlParameter("@ADDRESS",SqlDbType.VarChar,200),
                new SqlParameter("@IP", SqlDbType.VarChar, 50)
            };
            parms[0].Value = model.Username;
            parms[1].Value = model.MemberNickname;
            parms[2].Value = model.Password;
            parms[3].Value = model.Membername;
            parms[4].Value = model.UserType;
            parms[5].Value = model.Tel;
            parms[6].Value = model.Moblie;
            parms[7].Value = model.Postcode;
            parms[8].Value = model.Address;
            parms[9].Value = model.Ip;

            int id = 0;
            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        string s = SQLHelper.ExecuteScalar(trans, CommandType.Text, ADD_SQL, parms).ToString();

                        id = int.Parse(s);

                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }

            return id;
        }

        private const string UPDATE_SQL = "update COFCO_MEMBER_INFO set MEMBER_NICKNAME=@MEMBER_NICKNAME,MEMBER_NAME=@MEMBER_NAME,TEL=@TEL,MOBLIE=@MOBLIE,POSTCODE=@POSTCODE,ADDRESS=@ADDRESS where MEMBER_ID=@MEMBER_ID";
        //修改基本资料
        public bool Update(MemberInfoModel model)
        {
            SqlParameter[] parms = new SqlParameter[] { 
                
                new SqlParameter("@MEMBER_NICKNAME",SqlDbType.VarChar,100),
                
                new SqlParameter("@MEMBER_NAME",SqlDbType.VarChar,50),
                
                new SqlParameter("@TEL", SqlDbType.VarChar, 20),
                new SqlParameter("@MOBLIE",SqlDbType.VarChar,20),
                new SqlParameter("@POSTCODE",SqlDbType.VarChar,10),
                new SqlParameter("@ADDRESS",SqlDbType.VarChar,200),
                new SqlParameter("@IP", SqlDbType.VarChar, 50)
            };
            
            parms[0].Value = model.MemberNickname;
            
            parms[1].Value = model.Membername;
            
            parms[2].Value = model.Tel;
            parms[3].Value = model.Moblie;
            parms[4].Value = model.Postcode;
            parms[5].Value = model.Address;
            parms[6].Value = model.Memberid;
            int i = SQLHelper.ExecuteSql(UPDATE_SQL, parms);
            if (i > 0) return true;
            return false;
        }
        //修改登录时间
        public bool UpdateLastLoginTime(int mid, DateTime logintime)
        {
            int i = SQLHelper.ExecuteSql("update COFCO_MEMBER_INFO set LAST_LOGIN_TIME='" + logintime.ToLongTimeString() + "' where MEMBER_ID=" + mid, new SqlParameter[] { });
            if (i > 0) return true;
            return false;
        }
        //修改密码
        public bool UpdatePassword(int mid, string newpas)
        {
            int i = SQLHelper.ExecuteSql("update COFCO_MEMBER_INFO set PASSWORD='" + newpas + "' where MEMBER_ID=" + mid, new SqlParameter[]{});
            if (i > 0) return true;
            return false;
        }

        //修改状态
        public bool UpdateState(int mid, int state)
        {
            int i = SQLHelper.ExecuteSql("update COFCO_MEMBER_INFO set STATE=" + state + " where MEMBER_ID=" + mid, new SqlParameter[] { });
            if (i > 0) return true;
            return false;
        }
        private const string GET_MEMBER_SQL = "select MEMBER_ID,USERNAME,MEMBER_NICKNAME,PASSWORD,MEMBER_NAME,USER_TYPE,TEL,MOBLIE,POSTCODE,ADDRESS,REG_TIME,IP,LAST_LOGIN_TIME from COFCO_MEMBER_INFO where USERNAME=@USERNAME and USER_TYPE=@USER_TYPE";
        public MemberInfoModel GetMemberInfoModel(string username)
        {
            return GetMemberInfoModel(username, COFCO_MEMBER_TYPE);//官网会员
        }
        //获得用户信息
        public MemberInfoModel GetMemberInfoModel(string username, string userType)
        {
            MemberInfoModel mim = null;
            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter("@USERNAME", SqlDbType.VarChar, 50),
                new SqlParameter("@USER_TYPE",SqlDbType.VarChar, 20)
            };
            parms[0].Value = username;
            parms[1].Value = userType;
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, GET_MEMBER_SQL, parms))
            {
                if (rdr.Read())
                {
                    mim = new MemberInfoModel();

                    mim.Memberid = rdr.GetInt32(0);
                    mim.Username = rdr.GetString(1);
                    mim.MemberNickname = rdr.GetString(2);
                    mim.Password = rdr.GetString(3);
                    if (!rdr.IsDBNull(4)) mim.Membername = rdr.GetString(4);
                    if (!rdr.IsDBNull(5)) mim.UserType = rdr.GetString(5);
                    if (!rdr.IsDBNull(6)) mim.Tel = rdr.GetString(6);
                    if (!rdr.IsDBNull(7)) mim.Moblie = rdr.GetString(7);
                    if (!rdr.IsDBNull(8)) mim.Postcode = rdr.GetString(8);
                    if (!rdr.IsDBNull(9)) mim.Address = rdr.GetString(9);
                    if (!rdr.IsDBNull(10)) mim.RegTime = rdr.GetDateTime(10);
                    if (!rdr.IsDBNull(11)) mim.Ip = rdr.GetString(11);
                    if (!rdr.IsDBNull(12)) mim.LastLoginTime = rdr.GetDateTime(12);

                }
            }
            return mim;
        }
        private const string GET_MEMBER_ID_SQL = "select MEMBER_ID,USERNAME,MEMBER_NICKNAME,PASSWORD,MEMBER_NAME,USER_TYPE,TEL,MOBLIE,POSTCODE,ADDRESS,REG_TIME,IP,LAST_LOGIN_TIME,STATE from COFCO_MEMBER_INFO where MEMBER_ID=@MEMBER_ID";
        
        //获得用户信息
        public MemberInfoModel GetMemberInfoModel(int mid)
        {
            MemberInfoModel mim = null;
            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter("@MEMBER_ID", SqlDbType.Int, 4)
                
            };
            parms[0].Value = mid;
            
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, GET_MEMBER_ID_SQL, parms))
            {
                if (rdr.Read())
                {
                    mim = new MemberInfoModel();

                    mim.Memberid = rdr.GetInt32(0);
                    mim.Username = rdr.GetString(1);
                    mim.MemberNickname = rdr.GetString(2);
                    mim.Password = rdr.GetString(3);
                    if (!rdr.IsDBNull(4)) mim.Membername = rdr.GetString(4);
                    if (!rdr.IsDBNull(5)) mim.UserType = rdr.GetString(5);
                    if (!rdr.IsDBNull(6)) mim.Tel = rdr.GetString(6);
                    if (!rdr.IsDBNull(7)) mim.Moblie = rdr.GetString(7);
                    if (!rdr.IsDBNull(8)) mim.Postcode = rdr.GetString(8);
                    if (!rdr.IsDBNull(9)) mim.Address = rdr.GetString(9);
                    if (!rdr.IsDBNull(10)) mim.RegTime = rdr.GetDateTime(10);
                    if (!rdr.IsDBNull(11)) mim.Ip = rdr.GetString(11);
                    if (!rdr.IsDBNull(12)) mim.LastLoginTime = rdr.GetDateTime(12);

                    if (!rdr.IsDBNull(13)) mim.State = rdr.GetInt32(13);
                }
            }
            return mim;
        }
        public bool IsExists(string username)
        {
            return IsExists(username, COFCO_MEMBER_TYPE);
        }
        //是否存在
        public bool IsExists(string username, string userType)
        {
            if (GetMemberInfoModel(username, userType) != null)
                return true;
            else
                return false;
        }
        //查询会员
        public IList GetUserList(MemberInfoModel q)
        {
            string qsql = "select MEMBER_ID,USERNAME,MEMBER_NICKNAME,PASSWORD,MEMBER_NAME,USER_TYPE,TEL,MOBLIE,POSTCODE,ADDRESS,REG_TIME,IP,LAST_LOGIN_TIME,State from COFCO_MEMBER_INFO where 1=1 ";
            
            if (q != null)
            {
                if (q.Username != null && q.Username != "")
                {
                    qsql += " and username like '%" + StringUtil.ReplaceSecStr(q.Username) + "%'";
                }
            }
            qsql += " order by reg_time desc";
            IList datalist = new ArrayList();
            SqlParameter[] parms = new SqlParameter[] { };

            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, qsql, parms))
            {
                if (rdr.Read())
                {
                    MemberInfoModel mim = new MemberInfoModel();

                    mim.Memberid = rdr.GetInt32(0);
                    mim.Username = rdr.GetString(1);
                    mim.MemberNickname = rdr.GetString(2);
                    mim.Password = rdr.GetString(3);
                    if (!rdr.IsDBNull(4)) mim.Membername = rdr.GetString(4);
                    if (!rdr.IsDBNull(5)) mim.UserType = rdr.GetString(5);
                    if (!rdr.IsDBNull(6)) mim.Tel = rdr.GetString(6);
                    if (!rdr.IsDBNull(7)) mim.Moblie = rdr.GetString(7);
                    if (!rdr.IsDBNull(8)) mim.Postcode = rdr.GetString(8);
                    if (!rdr.IsDBNull(9)) mim.Address = rdr.GetString(9);
                    if (!rdr.IsDBNull(10)) mim.RegTime = rdr.GetDateTime(10);
                    if (!rdr.IsDBNull(11)) mim.Ip = rdr.GetString(11);
                    if (!rdr.IsDBNull(12)) mim.LastLoginTime = rdr.GetDateTime(12);
                    if (!rdr.IsDBNull(13)) mim.State = rdr.GetInt32(13);
                    datalist.Add(mim);

                }
            }
            return datalist;
        }

    }
}
